package io.renren.dao;

import io.renren.common.dao.BaseDao;
import io.renren.dto.FoodDTO;
import io.renren.dto.SupplierFoodDTO;
import io.renren.entity.FoodEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 食材表（辅材共用）
 *
 * @author 翟文闯 619556390@QQ.com
 * @since 1.0.0 2019-11-22
 */
@Mapper
public interface FoodDao extends BaseDao<FoodEntity> {

    /**
     * 供应商下所有商品
     * @date: 2019/11/25 13:51
     */
    @Select("select s.supplier_name, sf.supplier_id,cf.* from canteens_food cf, canteens_supplier_food sf,canteens_supplier s where " +
            "cf.food_id = sf.food_id and sf.supplier_id =s.supplier_id " +
            "${variety} " +
            "${type} " +
            "${name} "+
            "${supplierid} "
    )
    List<FoodDTO> supplierFoodId(String supplierid,String variety,String name,String type);
    /**
     * 根据商品名查询所有的供应商
     * @date: 2019/11/25 13:51
     */
    @Select("select C.supplier_id ,(select W.supplier_name from canteens_supplier W where W.supplier_id = C.supplier_id) as supplier_name " +
            "from canteens_food  A  , canteens_supplier_food  B ,canteens_supplier C " +
            "where  a.food_id = b.food_id  and B.supplier_id = C.supplier_id and " +
            " A.food_name  LIKE '%${name}%' ${types} GROUP BY supplier_id "
    )
    List<FoodDTO> getSupplierName(String name ,String types);
    /**
     * 根据商品名和供应商id 查询供应商下面的商品
     * @date: 2019/11/25 13:51
     */
    @Select("select A.*, C.supplier_id ,(select W.supplier_name from canteens_supplier W where W.supplier_id = C.supplier_id) as supplier_name " +
            "from canteens_food  A  , canteens_supplier_food  B ,canteens_supplier C " +
            "where  a.food_id = b.food_id  and B.supplier_id = C.supplier_id and " +
            " A.food_name  LIKE '%${name}%' and c.supplier_id = '${supplierId}'    GROUP BY a.food_id ")
    List<FoodDTO> getSupplierFood(String name,Long supplierId);
    @Select("select B.supplier_id ,B.purchase_num  from canteens_temporary_order A ,canteens_temporary_ordermx B " +
            " where A.temporary_id = B.temporary_id  and food_id ='${foodId}'  and  supplier_id ='${supplierId}' " +
            " and  A.sign = '${sign}' and A.ched_id = '${chedId}' and A.canteen_id = '${canteenId}'  ")
    FoodDTO getTemFood(Long foodId,Long supplierId,int sign,Long chedId, Long canteenId);
    @Select("select B.supplier_id ,B.purchase_num  from canteens_temporary_order A ,canteens_temporary_ordermx B " +
            " where A.temporary_id = B.temporary_id  and food_id ='${foodId}' " +
            " and  A.sign = '${sign}' and A.canteen_id = '${canteenId}'  ")
    FoodDTO getTemCollFood(Long foodId,int sign,Long chedId, Long canteenId);
    /**
     * 辅材领取表查询
     * @date: 2019/11/25 13:51
     */
    @Select("select fd.* from  canteens_food fd " +
            "LEFT JOIN canteens_foodmx  fx  on fx.food_id =fd.food_id  where   fd.type ='2' " +
            "${variety} " +
            "${name} " +
            "GROUP BY fx.food_id"
    )
    List<FoodDTO> selectCoolerFoodId(String variety,String name);

    @Select("SELECT MX.supplier_id,MX.purchase_num from canteens_stock_ordermx mx where mx.stock_order_id= '${id}'   and  MX.food_id = '${foodId}' ")
    FoodDTO gysOrdermx(long id,long foodId);
    @Select("SELECT MX.supplier_id,MX.purchase_num from canteens_auxiliary_materialsmx mx where mx.auxiliary_materials_id= '${id}' and MX.food_id  = '${foodId}' ")
    FoodDTO gysAuxiliarymx(long id,long foodId);
    @Select("SELECT MX.supplier_id,MX.purchase_num from canteens_small_canteen_purchasemx mx where mx.small_canteen_purchase_id = '${id}' and MX.food_id  = '${foodId}' ")
    FoodDTO gyssmallmx(long id,long foodId);
    @Select("SELECT MX.supplier_id,MX.purchase_num from canteens_collarmx mx where mx.collar_id = '${id}' and MX.food_id  = '${foodId}' mx.canteen_id = '${canteenId}'  ")
    FoodDTO gyscollarmx(long id,long foodId, Long canteenId);


    @Select(" select SUM(b.food_num)As  totaoNum,A.* from canteens_food  A , canteens_foodmx b  where  b.food_id = a.food_id and b.canteen_id = '${canteenId}' and A.food_name  LIKE '%${name}%'   GROUP BY food_id")
    List<FoodDTO> getFoodmx(String name, Long canteenId);

    @Select("select B.purchase_num  from canteens_temporary_order A ,canteens_temporary_ordermx B " +
            " where A.temporary_id = B.temporary_id  and food_id ='${foodId}'  " +
            " and  A.sign = '${sign}' and A.ched_id = '${chedId}' and A.canteen_id = '${canteenId}'  ")
    FoodDTO getcollFood(Long foodId,int sign,Long chedId, Long canteenId);
}